import backtrader as bt
import akshare as ak
import pandas as pd
import datetime  #
import os.path  # 路径管理
import sys  # 获取当前运行脚本的路径 (in argv[0])
import pymysql
from backtrader.feed import DataBase
from backtrader import date2num


class MyStrategy(bt.Strategy):
    params = dict(period1=20, period2=25, period3=5, period4=10)

    def __init__(self):
        sma1 = bt.ind.MovingAverageSimple(self.data.close, period=self.params.period1)
        sma2 = bt.ind.MovingAverageSimple(sma1, period=self.params.period2)

        # 通过算数极端创建新指标线
        something = sma2 - sma1 + self.data.close

        sma3 = bt.ind.MovingAverageSimple(something, period=self.params.period3)
        self.high_or_low = t.If(
            sma1 > self.data.close,
        )

        self.greater = sma3 > sma1

        self.buysig = bt.And(sma1 > self.data.close, sma1 > self.data.high)
        self.high_or_30 = bt.If(sma1 > self.data.close, 30.0, self.data.high)
        self.testIndicator = bt.DivByZero(self.data.open, self.data.close, zero=99999)
        self.testIndicator2 = bt.DivZeroByZero(
            self.data.open, self.data.close, 8888, 99999
        )


class MySQLData(DataBase):
    params = (
        ("fromdate", datetime.datetime.min),
        ("todate", datetime.datetime.max),
        ("ts_code", ""),
    )
    lines = ("turnover", "turnover_rate")

    def __init__(self):
        print("init")

    def start(self):
        print("start")
        conn = pymysql.connect(
            host="127.0.0.1",
            user="haojg",
            password="haojg",
            database="stocks",
            charset="utf8",
        )
        cursor = conn.cursor()

    def stop(self):
        print("stop")

    def _load(self):
        print("_load")

    def load_data_from_db(self, table, ts_code, start_time, end_time):
        """
        Args:
            table (_type_): 表名
            ts_code (_type_): 股票代码
            start_time (_type_): 起始时间
            end_time (_type_): 结束时间
        """
        print("load data")


conn = pymysql.connect(
    host="127.0.0.1",
    user="haojg",
    password="haojg",
    database="stocks",
    charset="utf8",
)

cursor = conn.cursor()

query_sql = """
select 
sd_date as date,      
sd_open as open,
sd_high as high,
sd_low as low,
sd_close as close,
sd_volume as volume
from 
t_stock_k_minute_data tskmd 
"""

query_sql2 = f""
"""
select 
sd_date as date,      
sd_open as open,
sd_high as high,
sd_low as low,
sd_close as close,
sd_volume as volume
from 
t_stock_k_minute_data tskmd 
"""

try:
    cursor.execute(query_sql)

    # 获取所有查询到的结果
    ret1 = cursor.fetchall()
    print(ret1)

    conn.commit()
except Exception as e:
    print("error = ", e)
    conn.rollback()

cursor.close()
conn.close()


if __name__ == "__main__":
    conn = pymysql.connect(
        host="127.0.0.1",
        user="haojg",
        password="haojg",
        database="stocks",
        charset="utf8",
    )

    cursor = conn.cursor()
    query_sql = """
    select 
    sd_date,sd_date_time,sd_code,sd_symbol,sd_name,       
    sd_open,sd_high,sd_low,sd_close,sd_volume,        
    sd_amount,sd_adjust_flag,sd_data_type,create_time,update_time 
    from 
    t_stock_k_minute_data tskmd 
    """
    try:
        cursor.execute(query_sql)

        # 获取所有查询到的结果
        ret1 = cursor.fetchall()
        print(ret1)

        conn.commit()
    except Exception as e:
        print("error = ", e)
        conn.rollback()

    cursor.close()
    conn.close()

    df = ak.stock_zh_a_hist(
        symbol="000001",
        period="daily",
        start_date="20230601",
        end_date="20230615",
        adjust="",
    )
    df = df.iloc[:, 0:6]
    df.日期 = pd.to_datetime(df.日期)
    print("df1 = ", df)
    # 列名记得这样定义好
    df.columns = ["Date", "Open", "Close", "High", "Low", "Volume"]
    print("df2 = ", df)
    df.set_index("Date", inplace=True)
    print("df3 = ", df)

    modpath = os.path.dirname(os.path.abspath(sys.argv[0]))
    datapath = os.path.join(modpath, "datas\\orcl-1995-2014.txt")

    data = bt.feeds.YahooFinanceCSVData(
        dataname=datapath,
        fromdate=datetime.datetime(2000, 1, 1),
        todate=datetime.datetime(2000, 1, 15),
        reversed=False,
    )

    print("data = ", data)

    # 创建Cerebro引擎
    cerebro = bt.Cerebro()

    # 为引入策略
    cerebro.addstrategy(MyStrategy)

    cerebro.adddata(df)

    # Cerebro引擎在后台创建broker(经纪人)，系统默认资金量为10000
    # 设置投资金额100000.0
    cerebro.broker.setcash(100000.0)

    # 设置每笔使用固定交易量
    cerebro.addsizer(bt.sizers.FixedSize, stake=10)

    # 设置佣金0.001
    cerebro.broker.setcommission(commission=0.001)

    cerebro.run()
    cerebro.plot()

    print("ending ok...")

    print("ok")
    # backtrader mysql
    # https://www.elecfans.com/d/2300686.html
